package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.PreparedStatement;

import dto.QuestionsDto;

public class QuestionsDao {

	public String insertQuestion(QuestionsDto question) {

		if (question == null)
			return "Insert failed! Input data is null.";
		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "INSERT INTO questions "
				+ "(question_no, question_desc, quizzes_quiz_no,  hint) "
				+ "VALUES (?,?,?,?)";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setInt(1, question.getQuestionNo());
			preparedStatement.setString(2, question.getQuestionDesc());
			preparedStatement.setInt(3, question.getQuizNo());
			preparedStatement.setString(4, question.getHint());

			preparedStatement.execute();
			response = "Inserted successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Insert failed! " + e.getMessage();
		}

		return response;
	}

	public String deleteQuestion(String questionNo) {

		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "DELETE FROM questions WHERE question_no = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, questionNo);

			preparedStatement.execute();
			response = "Deleted successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Delete failed! " + e.getMessage();
		}

		return response;
	}

	public String updateQuestion(QuestionsDto question) {

		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "UPDATE questions SET question_desc = ?"
				+ ", quizzes_quiz_no = ?"
				+ ", hint = ? WHERE question_no = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);

			preparedStatement.setString(1, question.getQuestionDesc());
			preparedStatement.setInt(2, question.getQuizNo());
			preparedStatement.setString(3, question.getHint());
			preparedStatement.setInt(4, question.getQuestionNo());

			preparedStatement.execute();
			response = "Updated successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Update failed! " + e.getMessage();
		}

		return response;
	}

	public QuestionsDto getQuestionById(String questionNo) {
		QuestionsDto question = null;

		Connection connection = DataProvider.getConnection();
		String sql = "SELECT * FROM questions WHERE question_no = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, questionNo);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				question = new QuestionsDto();
				question.setQuestionNo(resultSet.getInt("question_no"));
				question.setQuestionDesc(resultSet.getString("question_desc"));
				question.setQuizNo(resultSet.getInt("quizzes_quiz_no"));
				question.setHint(resultSet.getString("hint"));
			}

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return question;
	}

	public ArrayList<QuestionsDto> getAllQuestion() {

		ArrayList<QuestionsDto> listQuestion = new ArrayList<>();

		Connection connection = DataProvider.getConnection();
		String sql = "SELECT * FROM questions";

		Statement statement;
		try {
			statement = connection.createStatement();

			ResultSet resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				QuestionsDto question = new QuestionsDto();
				question.setQuestionNo(resultSet.getInt("question_no"));
				question.setQuestionDesc(resultSet.getString("question_desc"));
				question.setQuizNo(resultSet.getInt("quizzes_quiz_no"));
				question.setHint(resultSet.getString("hint"));
				listQuestion.add(question);
			}

			statement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listQuestion;
	}

}
